package jdbc;

import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;


import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 * Servlet implementation class showscore
 */
@WebServlet("/src/showscore")
public class showscore extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws  IOException {
		// TODO Auto-generated method stub
		//设置请求头
				response.setHeader("Access-Control-Allow-Origin", "*");//模板
			    response.setHeader("Access-Control-Allow-Methods", "GET, POST, PUT, DELETE");//模板
			    response.setHeader("Access-Control-Allow-Headers", "Content-Type");//模板
			    //设置编码格式
			    request.setCharacterEncoding("UTF-8");//模板
			    response.setCharacterEncoding("UTF-8");//模板
				
			    String current_page = request.getParameter("current_page1");
			    String recordsperpage = request.getParameter("recordsperpage1");
			    String student_id = request.getParameter("student_id");
			    String search = request.getParameter("search");
			    //int studentid = Integer.parseInt(student_id);
			    int currentPage = Integer.parseInt(current_page);
			    int recordsPerpage = Integer.parseInt(recordsperpage);
			    hello a = new hello();//模板
				try {//模板
					ResultSet b;
					if (search!=null) {
						
						b = a.doselect("SELECT COUNT(*) AS total_courses "
								+ "FROM sc s "
								+ "JOIN course c ON s.course_id = c.course_id "
								+ "JOIN teacher t ON c.course_teacher = t.teacher_id "
								+ "WHERE "
								+ "    s.student_id = ? "
								+ "    AND (t.teacher_name LIKE ? OR c.course_name LIKE ?);",student_id, "%"+search+"%","%"+search+"%");
					    if (b.next()) {
					    	response.getWriter().write(b.getString("total_courses")+";");
					    }
						b = a.doselect("SELECT "
								+ "    c.course_name, "
								+ "    t.teacher_name, "
								+ "    s.score "
								+ "FROM "
								+ "    sc s "
								+ "JOIN "
								+ "    course c ON s.course_id = c.course_id "
								+ "JOIN "
								+ "    teacher t ON c.course_teacher = t.teacher_id "
								+ "WHERE "
								+ "    s.student_id = ? "
								+ "    AND (t.teacher_name LIKE ? OR c.course_name LIKE ?) "
								+ "ORDER BY "
								+ "    c.course_id "
								+ "LIMIT "
								+ "    ?, ?;  -- 获取第一页数据，每页5条记录",student_id, "%"+search+"%","%"+search+"%", (currentPage-1)*recordsPerpage, recordsPerpage);
					}
					else {//这里是获取所有条数
						b = a.doselect("SELECT COUNT(*) AS total_courses "
								+ "FROM sc "
								+ "WHERE student_id = ?;" , student_id);
						if (b.next()) {
							response.getWriter().write(b.getString("total_courses")+";");
						}
						
						b = a.doselect("SELECT "
								+ "    c.course_name, "
								+ "    t.teacher_name, "
								+ "    s.score "
								+ "FROM "
								+ "    sc s "
								+ "JOIN "
								+ "    course c ON s.course_id = c.course_id "
								+ "JOIN "
								+ "    teacher t ON c.course_teacher = t.teacher_id "
								+ "WHERE "
								+ "    s.student_id = ? "
								+ "ORDER BY "
								+ "    c.course_id "
								+ "LIMIT "
								+ "    ?, ?;  -- 获取第一页数据，每页5条记录",student_id,(currentPage-1)*recordsPerpage, recordsPerpage);
					}
					while(b.next()) {//b代表查询结果，查询之后的那个表，next（）函数就是往下一行，逐行扫描
						response.getWriter().write(b.getString("course_name")+";");//这里表示的是往返回的那个response里面添加字符串，修改write括号后的内容即可
						response.getWriter().write(b.getString("teacher_name")+";");
						//response.getWriter().write(b.getString("course_id")+";");
						response.getWriter().write(b.getString("score")+";");
						//response.getWriter().write(b.getString("selected")+";");
					}//模板
					b.close();
				
				} catch (SQLException e) {//模板
					e.printStackTrace();//模板
				}//模板
			}//模板
}
